package com.yami.shop.dao;

import com.yami.shop.bean.dto.UserCardDto;
import com.yami.shop.bean.model.User;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.util.List;

public interface DistanceMapper {

    @Update("update tz_user set lng = #{lng},lat = #{lat} where user_id = #{uId}")
    int editLatitudeAndLongitude(@Param("uId") String uId,@Param("lng") Double lng,@Param("lat") Double lat);

    //根据距离排序
    @Select("SELECT us.*,  (st_distance(point(lng, lat), point(#{lng}, #{lat}) ) * 6378.137 * PI()/180) AS distance FROM tz_user us where status = 1 and lng IS not null ORDER BY distance")
    List<UserCardDto> queryDisUsers(@Param("lng") Double lng,@Param("lat") Double lat);

    //根据距离排序（男性用户只显示女性用户）
    @Select("SELECT us.*,  (st_distance(point(lng, lat), point(#{lng}, #{lat}) ) * 6378.137 * PI()/180) AS distance FROM tz_user us where sex = 'F' and status = 1 and lng IS not null ORDER BY distance")
    List<UserCardDto> queryDisUsersSex(@Param("lng") Double lng,@Param("lat") Double lat);

    //查询附近30km内的用户
    @Select("SELECT us.*, ( 6378.137 * acos( cos( radians(#{lat}) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(#{lng}) ) + sin( radians(#{lat}) ) * sin( radians( lat ) ) ) ) AS distance FROM tz_user us HAVING distance < 30 and status = 1 and lng IS not null ORDER BY is_online desc")
    List<UserCardDto> queryNeaUsers(@Param("lng") Double lng,@Param("lat") Double lat);

    //查询附近30km内的用户（男性用户只显示女性用户）
    @Select("SELECT us.*, ( 6378.137 * acos( cos( radians(#{lat}) ) * cos( radians( lat ) ) * cos( radians( lng ) - radians(#{lng}) ) + sin( radians(#{lat}) ) * sin( radians( lat ) ) ) ) AS distance FROM tz_user us HAVING distance < 30 and status = 1 and sex = 'F' and lng IS not null ORDER BY is_online desc")
    List<UserCardDto> queryNeaUsersSex(@Param("lng") Double lng,@Param("lat") Double lat);


}
